﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;

namespace WebQueryRole
{
    [Obsolete]
    public class JsonSqlVar
    {
        //  3	robin	all potential gene descriptions for each sequence	<TBD, requires uploading COG, PFAM tables first>	Read FHJ7DRN02G8M10.2 was annotated as COG0071 and I deal with it in a COG0071 PPLACER anaylsis. However, read FHJ7DRN02G8M10.2 may also be annotated as TIGRfamXXXX because this particular gene may be found in the COG database and the TIGRfam database. These databases overlap, but not completely. So either I want to run PPLACER 1 time because the COG and the TIGRfam should give similar results.
        public string short_desc { get; set; }
        public string long_desc { get; set; }
        public string sql_code { get; set; }

        [Obsolete]
        public JsonSqlVar()
        {
        }

        [Obsolete]
        public static JsonSqlVar SaveSqlVar(string shortDesc, string longDesc, string sqlCode, SqlConnection conn)
        {
            // return type
            JsonSqlVar var = new JsonSqlVar();
            var.short_desc = shortDesc;
            var.long_desc = longDesc;
            var.sql_code = sqlCode;

            // first create the record
            SqlCommand cmd = new SqlCommand("sp_CreateUpdateSqlVar", conn);
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("@sql_code", SqlDbType.VarChar).Value = sqlCode;
            cmd.Parameters.Add("@short_desc", SqlDbType.VarChar).Value = shortDesc;
            cmd.Parameters.Add("@long_desc", SqlDbType.VarChar).Value = longDesc;
            
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                // good place for a breakpoint to catch the error
                throw e;
            }

            return var;
        }

        /// <summary>
        /// Delete this record from our table
        /// </summary>
        /// <param name="shortDesc"></param>
        /// <param name="conn"></param>
        [Obsolete]
        public static void DeleteSqlVar(string shortDesc, SqlConnection conn)
        {
            SqlCommand cmd = new SqlCommand("sp_DeleteSqlVar", conn);
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("@short_desc", SqlDbType.VarChar).Value = shortDesc;

            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                // good place for a breakpoint to catch the error
                throw e;
            }
        }
        [Obsolete]
        public static List<JsonSqlVar> LoadStore(SqlConnection conn)
        {
            List<JsonSqlVar> cans = new List<JsonSqlVar>();

            // for now assume its always a select query
            SqlCommand cmd = new SqlCommand("SELECT * FROM bp_sql_var", conn);

            SqlDataReader reader = null;
            try
            {
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    JsonSqlVar can = new JsonSqlVar();
                    can.sql_code = reader["sql_code"].ToString(); ;
                    can.short_desc = reader["short_desc"].ToString();
                    can.long_desc = reader["long_desc"].ToString();
                    cans.Add(can);
                }
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
            }

            return cans;
        }
    }
}
